import pandas as pd

df1=pd.read_csv('1.csv')
df2=pd.read_csv('2.csv')
df=df1.merge(df2,on='省辖市')
df_sum=pd.pivot_table(df,index='省辖市',aggfunc='sum',fill_value=0)
df_hwd=pd.pivot_table(df,index='省辖市',columns='是否黑窝点',values='案件状态',aggfunc='count',fill_value=0)
df_yj=pd.pivot_table(df,index='省辖市',columns='案件状态',values='是否黑窝点',aggfunc='count',fill_value=0)
df=df_sum.merge(df_hwd,on="省辖市").merge(df_yj,on="省辖市")
df.sort_values('bj',inplace=True)
df.drop(columns=['bj','否','立案','结案'],inplace=True)
df.rename(columns={'是':'黑窝点数','移交':'移交案件数'},inplace=True)
df.reset_index(drop=False,inplace=True)
ds=df.sum()
ds[0]='合计'
df=df.append(ds,ignore_index=True)
# print(df)
# print(df.to_dict(orient='dict'))
# print(df.to_dict(orient='list'))
# print(df.to_dict(orient='series'))
# print(df.to_dict(orient='records'))
# print(df.to_dict(orient='index'))
print(df.to_dict(orient='split'))
